const csv = require('csvtojson')
const fs = require('fs')

async function checkNumber() {
    let idAndNumberJsonArray = await csv({
        noheader: true,
        delimiter: '|'
    }).fromFile('D:/idAndNumber.txt')

    let numberJsonArray = await csv({
        noheader: true,
        delimiter: '|'
    }).fromFile('D:/number.txt')
    
    console.log('id 和 号码做一条记录共有: ', idAndNumberJsonArray.length)
    console.log('号码总共有: ', numberJsonArray.length)
    for (let i = 0; i < idAndNumberJsonArray.length; i++) {
        let containInNumberArray = false;
        for (let j = 0; j < numberJsonArray.length; j++) {
            if (numberJsonArray[j].field1 == idAndNumberJsonArray[i].field2) {
                containInNumberArray = true;
            }
        }
        // console.log(`containInNumberArray result: ${containInNumberArray}, number: ${idAndNumberJsonArray[i].field2}`)
        if (!containInNumberArray) {
            console.log('containInNumberArray result Exception --------------------------------------------------------------------------------')
            break;
        }
        
        
    }
    console.log('done')

    let idDeleteJsonArray = await csv({
        noheader: true,
        delimiter: '|'
    }).fromFile('D:/idDelete.txt')

    console.log('要删除的id数量: ', idDeleteJsonArray.length)
    for (let i = 0; i < idDeleteJsonArray.length; i++) {
        let idDeleteContainIn = false;
        for (let j = 0; j < idAndNumberJsonArray.length; j++) {
            if (idDeleteJsonArray[i].field1 == idAndNumberJsonArray[j].field1) {
                idDeleteContainIn = true;
            }
        }
        // console.log(`id：${idDeleteJsonArray[i].field1}, 是否存在：${idDeleteContainIn}`)
        if (!idDeleteContainIn) {
            console.log('delete id Exception --------------------------------------------------------')
            break;
        }
    }
}


async function generateDeleteSql() {
    let idDeleteJsonArray = await csv({
        noheader: true,
        delimiter: '|'
    }).fromFile('D:/idDelete.txt')
    let result = 'delete from msisdn_number_lock where id in ('
    for (let i = 0; i < idDeleteJsonArray.length; i++) {
        if (i == idDeleteJsonArray.length - 1) {
            result += idDeleteJsonArray[i].field1
        } else {
            result += idDeleteJsonArray[i].field1 + ', '
        }
    }
    result += ');'
    fs.writeFileSync("D:/deleteSql.txt", result)
}

async function generateSelectSql() {
    let idDeleteJsonArray = await csv({
        noheader: true,
        delimiter: '|'
    }).fromFile('D:/idDelete.txt')
    let result = 'select * from msisdn_number_lock where id in ('
    for (let i = 0; i < idDeleteJsonArray.length; i++) {
        if (i == idDeleteJsonArray.length - 1) {
            result += idDeleteJsonArray[i].field1
        } else {
            result += idDeleteJsonArray[i].field1 + ', '
        }
    }
    result += ');'
    fs.writeFileSync("D:/selectSql.txt", result)
}



// checkNumber();

// generateDeleteSql();

generateSelectSql();